/**
 * 测试 t_product_videos 表功能
 * 包括表创建、数据插入、关联查询等
 */

const mysql = require('mysql2/promise');
require('dotenv').config();

const DB_CONFIG = {
    host: process.env.DB_HOST || 'localhost',
    port: process.env.DB_PORT || 3306,
    user: process.env.DB_USER || 'root',
    password: process.env.DB_PASSWORD || '',
    database: process.env.DB_NAME || 'tkshop'
};

async function testProductVideosTable() {
    let connection;

    try {
        console.log('🔍 开始测试 t_product_videos 表功能...\n');

        // 连接数据库
        console.log('📡 连接数据库...');
        connection = await mysql.createConnection(DB_CONFIG);
        console.log('✅ 数据库连接成功\n');

        // 检查表是否存在
        await checkTableExists(connection);

        // 检查表结构
        await checkTableStructure(connection);

        // 检查索引
        await checkTableIndexes(connection);

        // 检查外键约束
        await checkForeignKeys(connection);

        // 测试数据操作
        await testDataOperations(connection);

        // 测试关联查询
        await testJoinQueries(connection);

        console.log('🎉 所有测试通过！t_product_videos 表功能正常');

    } catch (error) {
        console.error('❌ 测试失败:', error.message);

        if (error.code === 'ER_NO_SUCH_TABLE') {
            console.log('💡 提示：请先运行 npm run db:init-videos 创建表');
        } else if (error.code === 'ER_BAD_DB_ERROR') {
            console.log('💡 提示：请先运行 npm run db:setup 创建数据库');
        }

        process.exit(1);
    } finally {
        if (connection) {
            await connection.end();
            console.log('📡 数据库连接已关闭');
        }
    }
}

/**
 * 检查表是否存在
 */
async function checkTableExists(connection) {
    console.log('🔍 检查表是否存在...');

    const [tables] = await connection.query(`
    SELECT TABLE_NAME 
    FROM INFORMATION_SCHEMA.TABLES 
    WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 't_product_videos'
  `, [DB_CONFIG.database]);

    if (tables.length === 0) {
        throw new Error('t_product_videos 表不存在');
    }

    console.log('✅ t_product_videos 表存在\n');
}

/**
 * 检查表结构
 */
async function checkTableStructure(connection) {
    console.log('🔍 检查表结构...');

    const [columns] = await connection.query(`
    SELECT COLUMN_NAME, DATA_TYPE, IS_NULLABLE, COLUMN_KEY, COLUMN_COMMENT
    FROM INFORMATION_SCHEMA.COLUMNS 
    WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 't_product_videos'
    ORDER BY ORDINAL_POSITION
  `, [DB_CONFIG.database]);

    const expectedColumns = [
        'id', 'origin_cover', 'product_id', 'desc', 'video_duration',
        'video_link', 'total_estimated_sales_amount', 'total_estimated_sales_volume',
        'play_count', 'digg_count', 'comment_count', 'share_count',
        'author_last_time', 'author_last_time_stamp', 'author_id',
        'author_unique_id', 'author_nickname', 'sys_country_country',
        'sys_country_country_cn', 'sys_country_country_code',
        'author_follower_count', 'author_homepage', 'createTime', 'updateTime'
    ];

    console.log(`   表中共有 ${columns.length} 个字段`);

    for (const expectedCol of expectedColumns) {
        const found = columns.find(col => col.COLUMN_NAME === expectedCol);
        if (found) {
            console.log(`   ✅ ${expectedCol} (${found.DATA_TYPE})`);
        } else {
            console.log(`   ❌ 缺少字段: ${expectedCol}`);
        }
    }

    console.log('✅ 表结构检查完成\n');
}

/**
 * 检查索引
 */
async function checkTableIndexes(connection) {
    console.log('🔍 检查表索引...');

    const [indexes] = await connection.query(`
    SELECT INDEX_NAME, COLUMN_NAME, NON_UNIQUE
    FROM INFORMATION_SCHEMA.STATISTICS 
    WHERE TABLE_SCHEMA = ? AND TABLE_NAME = 't_product_videos'
    ORDER BY INDEX_NAME, SEQ_IN_INDEX
  `, [DB_CONFIG.database]);

    const indexGroups = {};
    indexes.forEach(index => {
        if (!indexGroups[index.INDEX_NAME]) {
            indexGroups[index.INDEX_NAME] = [];
        }
        indexGroups[index.INDEX_NAME].push(index.COLUMN_NAME);
    });

    console.log(`   表中共有 ${Object.keys(indexGroups).length} 个索引:`);
    Object.entries(indexGroups).forEach(([indexName, columns]) => {
        console.log(`   📌 ${indexName}: [${columns.join(', ')}]`);
    });

    console.log('✅ 索引检查完成\n');
}

/**
 * 检查外键约束
 */
async function checkForeignKeys(connection) {
    console.log('🔍 检查外键约束...');

    const [fks] = await connection.query(`
    SELECT 
      CONSTRAINT_NAME,
      COLUMN_NAME,
      REFERENCED_TABLE_NAME,
      REFERENCED_COLUMN_NAME
    FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE 
    WHERE TABLE_SCHEMA = ? 
      AND TABLE_NAME = 't_product_videos'
      AND REFERENCED_TABLE_NAME IS NOT NULL
  `, [DB_CONFIG.database]);

    if (fks.length > 0) {
        console.log(`   外键约束数量: ${fks.length}`);
        fks.forEach(fk => {
            console.log(`   🔗 ${fk.COLUMN_NAME} -> ${fk.REFERENCED_TABLE_NAME}.${fk.REFERENCED_COLUMN_NAME}`);
        });
    } else {
        console.log('   ⚠️  未找到外键约束');
    }

    console.log('✅ 外键约束检查完成\n');
}

/**
 * 测试数据操作
 */
async function testDataOperations(connection) {
    console.log('🔍 测试数据操作...');

    // 检查当前数据量
    const [countResult] = await connection.query('SELECT COUNT(*) as count FROM t_product_videos');
    const currentCount = countResult[0].count;
    console.log(`   当前表中有 ${currentCount} 条数据`);

    // 测试插入数据（如果商品表有数据的话）
    try {
        const [products] = await connection.query('SELECT id FROM t_product LIMIT 1');

        if (products.length > 0) {
            const productId = products[0].id;
            const testVideoId = `test_${Date.now()}`;

            // 插入测试数据
            await connection.query(`
        INSERT INTO t_product_videos (
          id, product_id, \`desc\`, video_duration, play_count,
          digg_count, author_unique_id, author_nickname
        ) VALUES (?, ?, ?, ?, ?, ?, ?, ?)
      `, [
                testVideoId,
                productId,
                '测试视频描述',
                30,
                1000,
                50,
                'test_user',
                '测试用户'
            ]);

            console.log(`   ✅ 成功插入测试数据，视频ID: ${testVideoId}`);

            // 查询并删除测试数据
            const [inserted] = await connection.query('SELECT * FROM t_product_videos WHERE id = ?', [testVideoId]);
            if (inserted.length > 0) {
                console.log(`   ✅ 成功查询到插入的数据`);
            }

            await connection.query('DELETE FROM t_product_videos WHERE id = ?', [testVideoId]);
            console.log(`   ✅ 成功删除测试数据`);

        } else {
            console.log('   ⚠️  商品表为空，跳过数据插入测试');
        }

    } catch (error) {
        console.log(`   ⚠️  数据操作测试失败: ${error.message}`);
    }

    console.log('✅ 数据操作测试完成\n');
}

/**
 * 测试关联查询
 */
async function testJoinQueries(connection) {
    console.log('🔍 测试关联查询...');

    try {
        // 测试左连接查询
        const [joinResult] = await connection.query(`
      SELECT 
        p.id as product_id,
        p.productName,
        COUNT(v.id) as video_count
      FROM t_product p
      LEFT JOIN t_product_videos v ON p.id = v.product_id
      GROUP BY p.id, p.productName
      LIMIT 5
    `);

        console.log(`   关联查询结果数量: ${joinResult.length}`);

        if (joinResult.length > 0) {
            console.log('   关联查询示例:');
            joinResult.slice(0, 3).forEach(row => {
                console.log(`   📦 商品: ${row.productName} -> 视频数: ${row.video_count}`);
            });
        }

        console.log('✅ 关联查询测试完成\n');

    } catch (error) {
        console.log(`   ⚠️  关联查询测试失败: ${error.message}\n`);
    }
}

// 运行测试
if (require.main === module) {
    testProductVideosTable();
}

module.exports = {
    testProductVideosTable
};